/*
 * Created on 11.06.2004
 *
 * To change the template for this generated file go to
 * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
 */
package de.localsport.util;

import java.sql.*;
import java.util.*;

/**
 * @author Stephan
 *
 * To change the template for this generated type comment go to
 * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
 */
public class MemberCharts {

	private static String SQL =
	"SELECT distinct " + 
		"members.nickname as nickname, " +
		"members.member_login as m_login, " +
		"members.first_name as m_first, " +
		"members.last_name as m_last, " +
		"members.email as m_email, " +		
		"sum(bets.score) as m_points " +
	"FROM members LEFT JOIN bets ON members.member_id = bets.member_id " + 
	"WHERE members.registered = 1 " +
	"GROUP BY m_login ORDER BY m_points DESC";	 
	
	private List MemberList = Collections.synchronizedList(new ArrayList());  

	public String showRanking() {
  
		Connection conn;
		Statement stat;
    
		try {
			conn = ConnPool.getConnection();
		  	stat = conn.createStatement();
		  	
		  	ResultSet rs = stat.executeQuery(SQL);
      
		  	if (! rs.next()) {
				rs.close();
				stat.close();
				conn.close();
				return "";
		  	}
            
		  	String ret = buildOutput(rs);
      			
			// Clean up
			rs.close();
			stat.close();
			conn.close();
		     
		  	return ret;
		}
		catch (Exception e) {
		  	return "";
		}
	}

	private String buildOutput (ResultSet ml) {	
		String tmp = "&nbsp;<br><br>" +
		   	"<table width=\"70%\" bgcolor=\"#000000\">" +
		   	"<tr>\n<td style=\"background-color: #ffcc66; text-align: Center\" colspan=\"6\"><a name=\"Members\"><font style=\"font-size: 14pt; font-weight: bold\">Rangliste</font></a></td>\n</tr>" +
			"<tr>\n" +
			"<td width=\"100\" style=\"background-color: #FFCC66\"><center><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Platzierung</font></center></td>\n" +
			"<td style=\"background-color: #FFCC66\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Nickname</font></td>\n" +
			"<td style=\"background-color: #FFCC66\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Vorname</font></td>\n" +
			"<td style=\"background-color: #FFCC66\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Name</font></td>\n" +
			"<td style=\"background-color: #FFCC66\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Email</font></td>\n" +			
			"<td style=\"background-color: #FFCC66\"><font style=\"font-size: 12pt; color: #000000; font-weight: bold\">Punkte</font></td>\n" +
			"</tr>";
		
		try {
			//Init
			int pos = 1;  
			ml.beforeFirst();		
			int last_score = 0;
			
			while (ml.next()) {      	  										
				if (last_score > ml.getInt("m_points")) {
					pos++;
				}
				
				last_score = ml.getInt("m_points");
				
				tmp += 	"<tr>" +
					"<td style=\"background-color: #FFFFEE\">" + 
					"<center><font style=\"font-size: 12pt; color: #000000\"><b>" + new Integer(pos).toString() + "</b></font></center>" +
					"</td>" + 
					"<td style=\"background-color: #FFFFEE\">" + 
				  	"<font style=\"font-size: 12pt; color: #000000\"><b>" + (isNull(ml.getString("nickname")) ? "" : ml.getString("nickname")) + "</b></font>" +
				  	"</td>" +					
				  	"<td style=\"background-color: #FFFFEE\">" +
				  	"<font style=\"font-size: 12pt; color: #000000\"><b>" + (isNull(ml.getString("m_first")) ? "" : ml.getString("m_first")) + "&nbsp;</b></font>" +
				  	"</td>" +      
				  	"<td style=\"background-color: #FFFFEE\">" +
				  	"<font style=\"font-size: 12pt; color: #000000\"><b>" + (isNull(ml.getString("m_last")) ? "" : ml.getString("m_last")) + "&nbsp;</b></font>" +
				  	"</td>" +
					"<td style=\"background-color: #FFFFEE\">"; 
					if (!isNull(ml.getString("m_email"))) {
						tmp +=  "<a href=\"mailto:" + ml.getString("m_email") + "\">" +
								"<font style=\"font-size: 12pt; color: #000000\"><b>" + ml.getString("m_email") + "&nbsp;</b></font></a>"; 
					}
				tmp += "</td>" +
					"<td style=\"background-color: #FFFFEE\">" +
					"<font style=\"font-size: 12pt; color: #000000\"><b>" + (isNull(ml.getString("m_points")) ? "0" : ml.getString("m_points")) + "&nbsp;</b></font>" +
					"</td>" +
      				"</tr>";
			}		
		} catch (Exception e) {
			return "";
		}
		
		tmp += "</table>&nbsp;<br><br>";		
		return tmp;		
	}


	public boolean isNull(String s) {
		return (s==null || s.equals(""));
	}

}
